package org.hfoss.POCT;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.provider.BaseColumns;
import android.provider.MediaStore;
import android.util.Log;


public class MyDBHelper extends SQLiteOpenHelper {
	/*
	 * Add new tables here.
	 */
	private static final String DBName ="poct";
	public static final int DBVersion = 2;
	private static final String TAG = "MyDBHelper";

	/**
	 *  The primary table
	 */
	public static final String BUILDING_TABLE_NAME = "buildings";
	public static final String KEY_ID = "_id";
	public static final String KEY_NAME = "name";
	public static final String KEY_IDENTIFIER = "identifier";
	public static final String KEY_DESCRIPTION = "description";
	public static final String KEY_LATITUDE = "latitude";
	public static final String KEY_LONGITUDE = "longitude";

	/**
	 * Table and Fields for the photos table
	 */
	public static final String PHOTO_TABLE_NAME = "photos";
	public static final String KEY_PHOTO_ID = "_id";
	public static final String KEY_BUILDING_ID = "buildingId";
	public static final String KEY_IMAGE_URI = "imageUri";
	public static final String KEY_PHOTO_THUMBNAIL_URI = "imageThumbnailUri";

	/**
	 * Table and Fields for the video table
	 */
	public static final String VIDEO_TABLE_NAME = "videos";
	public static final String KEY_VIDEO_ID = "_id";
	public static final String KEY_VIDEO_URI = "videoUri";
	public static final String KEY_VIDEO_THUMBNAIL_URI = "videoThumbnailUri";

	/**
	 * Table and Fields for the audio table
	 */
	public static final String AUDIO_TABLE_NAME = "audios";
	public static final String KEY_AUDIO_ID = "_id";
	public static final String KEY_AUDIO_URI = "audioUri";

	public static final String[] list_building_data_row = { 
		KEY_ID,
		KEY_NAME,
	};

	public static final int[] list_building_view_row = {
		//TODO: Getting this from James's building_list.xml 
		R.id.building_default_image, 
		R.id.name_id, 

	};
	
//	public static final String[] list_video_row_data = {
//		KEY_VIDEO_ID,
//		KEY_VIDEO_URI,
//	};
//
//	public static final int[] list_video_row_views = {
//		R.id.show_video_image,
//		R.id.video_uri_id
//	};
//
//	public static final String[] list_audio_row_data = {
//		KEY_AUDIO_ID,
//		KEY_AUDIO_URI
//	};
//
//	public static final int[] list_audio_row_views = {
//		R.id.show_audio_image,
//		R.id.audio_uri_id
//	};

	/**
	 * Finds table creation sql statement. 
	 *  To clean up: get those string literals out of there
	 */
	private static final String CREATE_BUILDINGS_TABLE = "CREATE TABLE "
		+ BUILDING_TABLE_NAME  
		+ " (" + KEY_ID + " integer primary key autoincrement, "
		+ KEY_NAME + " text, "
		+ KEY_DESCRIPTION + " text, "
		+ KEY_LATITUDE + " double, "
		+ KEY_LONGITUDE + " double, "
		+ KEY_IDENTIFIER + " text, " /* for barcodes*/
		+ KEY_IMAGE_URI + " text, "
		+ KEY_PHOTO_THUMBNAIL_URI + " text "
		+ KEY_VIDEO_URI + " text, "
		+ KEY_VIDEO_THUMBNAIL_URI + " text, "
		+ KEY_AUDIO_URI + " text, "
		+ ");";

	private static final String CREATE_IMAGES_TABLE = "CREATE TABLE "
		+ PHOTO_TABLE_NAME  
		+ " (" + KEY_ID + " integer primary key autoincrement, "  // User Key
		+ KEY_BUILDING_ID + " integer, "      // User Key
		+ KEY_IMAGE_URI + " text, "      // The image's URI
		+ KEY_PHOTO_THUMBNAIL_URI + " text "      // The thumbnail's URI
		+ ");";

	private static final String CREATE_VIDEO_TABLE = "CREATE TABLE "
		+ VIDEO_TABLE_NAME  
		+ " (" + KEY_ID + " integer primary key autoincrement, "  // User Key
		+ KEY_BUILDING_ID + " integer, "      // User Key
		+ KEY_VIDEO_URI + " text, "      // The video's URI
		+ KEY_VIDEO_THUMBNAIL_URI + " text "      // The thumbnail's URI
		+ ");";

	private static final String CREATE_AUDIO_TABLE = "CREATE TABLE "
		+ AUDIO_TABLE_NAME  
		+ " (" + KEY_ID + " integer primary key autoincrement, "  // User Key
		+ KEY_BUILDING_ID + " integer, "      // User Key
		+ KEY_AUDIO_URI + " text "      // The audio's URI
		+ ");";
	
	private Context mContext;   // The Activity
	private SQLiteDatabase mDb;  // Pointer to the DB	

	public MyDBHelper(Context context) {
		super(context, DBName, null, DBVersion);
		this.mContext= context;
	}

	/**
	 * This method is called only when the DB is first created.
	 */
	@Override
	public void onCreate(SQLiteDatabase db) throws SQLException {
		db.execSQL(CREATE_BUILDINGS_TABLE);
		db.execSQL(CREATE_IMAGES_TABLE);
		db.execSQL(CREATE_VIDEO_TABLE);
		db.execSQL(CREATE_AUDIO_TABLE);
	}

	/**
	 * This method is called when the DB needs to be upgraded -- not
	 *   sure when that is??
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
				+ newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS " + BUILDING_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + PHOTO_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + VIDEO_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + AUDIO_TABLE_NAME);
		onCreate(db);		
	}

	/** 
	 * This method will return a Cursor with rows of data for all Building.
	 * @return
	 */
	public Cursor fetchAllBuildings() {
		mDb = getReadableDatabase();
		return mDb.query(BUILDING_TABLE_NAME,null, null, null, null, null, null);
	}
	
	public String getName(int id) {
		String[] columns = {KEY_NAME};
		String[] selectionArgs = null;
		String groupBy = null, having = null, orderBy = null;
		mDb = getWritableDatabase();
		Cursor cursor = mDb.query(BUILDING_TABLE_NAME, columns, KEY_BUILDING_ID + "=" + id, selectionArgs, groupBy, having, orderBy);
		if (cursor.getCount() == 1){
			cursor.moveToFirst();
			String building_name = cursor.getString(cursor.getColumnIndex(columns[0]));
			return building_name;
		}
		return null;
	}
	
	public Cursor getLongitude(int id){
		String[] columns = {KEY_LONGITUDE, KEY_LATITUDE};
		String[] selectionArgs = null;
		String groupBy = null, having = null, orderBy = null;
		mDb = getWritableDatabase();
		Cursor cursor = mDb.query(BUILDING_TABLE_NAME, columns, KEY_BUILDING_ID + "=" + id, selectionArgs, groupBy, having, orderBy);
		return cursor;
	}


	public String getDescription(int id) {
		String[] columns = {KEY_DESCRIPTION};
		String[] selectionArgs = null;
		String groupBy = null, having = null, orderBy = null;
		mDb = getWritableDatabase();
		Cursor cursor = mDb.query(BUILDING_TABLE_NAME, columns, KEY_BUILDING_ID + "=" + id, selectionArgs, groupBy, having, orderBy);
		if (cursor.getCount() == 1){
			cursor.moveToFirst();
			String building_description = cursor.getString(cursor.getColumnIndex(columns[0]));
			return building_description;
		}
		return null;
	}

	public Cursor fetchSelectedColumns(String[] columns){
		mDb = getReadableDatabase();
		Cursor c = null;
		c = mDb.query(BUILDING_TABLE_NAME, columns, null, null, null, null, null); //  NOTE WELL: Closing causes an error in cursor mgmt
		return c;
	}


	/**
	 * Retrieves a photo from the database, based on a specific building and position.
	 * @param buildingId the building whose photo is to be retrieved
	 * @param position the specific position [row id, list position, etc] the photo is located at
	 * @return Uri to the photo
	 */
	
	public Uri getPhotoUriByPosition(long buildingId, int position) {
		mDb = getWritableDatabase();
		Cursor cursor = getImagesCursor(buildingId);
		Log.i(TAG, "column count = " + cursor.getColumnCount());
		Log.i(TAG, "count = " + cursor.getCount());

		if (cursor.moveToPosition(position)) {
			String s = cursor.getString(cursor.getColumnIndex(KEY_IMAGE_URI));
			Log.i(TAG, "uri = " + s);
			mDb.close();
			cursor.close();
			if (s != null) {
				return Uri.parse(s);
			}
		} else {
			Log.e(TAG, "cursor could not move to position: " + position);
		}

		return null;
	}

	/**
	 * Retrieves a video from the database, based on a specific building and position.
	 * @param buildingId the building whose video is to be retrieved
	 * @param position the specific position [row id, list position, etc] the video is located at
	 * @return Uri to the video
	 */
	public Uri getVideoUriByPosition(long buildingId, int position) {
		mDb = getWritableDatabase();
		Cursor cursor = getVideosCursor(buildingId);

		if (cursor.moveToPosition(position)) {
			String s = cursor.getString(cursor.getColumnIndex(KEY_VIDEO_URI));
			cursor.close();
			Log.i(TAG, "uri = " + s);
			mDb.close();
			cursor.close();
			if (s != null) {
				return Uri.parse(s);
			}
		} else {
			Log.e(TAG, "cursor could not move to position: " + position);
		}

		return null;
	}

	/**
	 * Retrieves an audio clip from the database, based on a specific building and position.
	 * @param buildingId the building whose id is to be retrieved
	 * @param position the specific position [row id, list position, etc] the audio clip is located at
	 * @return Uri to the audio clip
	 */
	public Uri getAudioUriByPosition(long buildingId, int position) {
		mDb = getWritableDatabase();
		Cursor cursor = getAudiosCursor(buildingId);

		if (cursor.moveToPosition(position)) {
			String s = cursor.getString(cursor.getColumnIndex(KEY_AUDIO_URI));
			cursor.close();
			Log.i(TAG, "uri = " + s);
			mDb.close();
			cursor.close();
			if (s != null) {
				return Uri.parse(s);
			}
		}

		return null;
	}
	

	/**
	 * This method is called from a Building object.  It queries the DB with the 
	 *   Buiding's ID. It constructs a ContentsValue hash table and returns it.
	 *   Note that it closes the DB and the Cursor -- to prevent leaks.
	 *   TODO:  It should handle a failed query with an Exception 
	 * @param id the building's id
	 * @return
	 */
	public ContentValues fetchBuildingData(long id) {
		String[] columns = null;
		ContentValues values = fetchBuildingColumns(id, columns);
		return values;
	}

	public Cursor getImagesCursor(long id) {
		mDb = getReadableDatabase(); 
		String[] columns = {KEY_IMAGE_URI, KEY_PHOTO_THUMBNAIL_URI};
		String[] selectionArgs = null;
		String groupBy = null, having = null, orderBy = null;
		Cursor cursor = mDb.query(PHOTO_TABLE_NAME, columns, KEY_BUILDING_ID + "=" + id, selectionArgs, groupBy, having, orderBy);
		return cursor;
	}

	public Cursor getVideosCursor(long id) {
		mDb = getReadableDatabase(); 
		String[] columns = { KEY_BUILDING_ID, KEY_VIDEO_ID, KEY_VIDEO_URI };
		String[] selectionArgs = null;
		String groupBy = null, having = null, orderBy = null;
		Cursor cursor = mDb.query(VIDEO_TABLE_NAME, columns, KEY_BUILDING_ID + "=" + id, selectionArgs, groupBy, having, orderBy);
		return cursor;
	}

	public Cursor getAudiosCursor(long id) {
		mDb = getReadableDatabase(); 
		String[] columns = { KEY_BUILDING_ID, KEY_AUDIO_ID, KEY_AUDIO_URI };
		String[] selectionArgs = null;
		String groupBy = null, having = null, orderBy = null;
		Cursor cursor = mDb.query(AUDIO_TABLE_NAME, columns, KEY_BUILDING_ID + "=" + id, selectionArgs, groupBy, having, orderBy);
		return cursor;
	}

	public ContentValues getImages(long id) {
		mDb = getReadableDatabase();
		Cursor cursor = getImagesCursor(id);
		cursor.moveToFirst();
		ContentValues values = new ContentValues();
		Log.i(TAG, "Images count = " + cursor.getCount() + " for _id = " + id);
		if (cursor.getCount() != 0)
			values = getValuesFromRow(cursor);
		cursor.close();
		mDb.close();
		return values;
	}

	public ContentValues getVideos(long id) {
		mDb = getReadableDatabase();
		Cursor cursor = getVideosCursor(id);
		cursor.moveToFirst();
		ContentValues values = new ContentValues();
		Log.i(TAG, "Videos count = " + cursor.getCount() + " for _id = " + id);
		if (cursor.getCount() != 0) {
			values = getValuesFromRow(cursor);
		}
		cursor.close();
		mDb.close();
		return values;
	}

	public ContentValues getAudios(long id) {
		mDb = getReadableDatabase();
		Cursor cursor = getAudiosCursor(id);
		cursor.moveToFirst();
		ContentValues values = new ContentValues();
		Log.i(TAG, "Audio clips count = " + cursor.getCount() + " for _id = " + id);
		if (cursor.getCount() != 0) {
			values = getValuesFromRow(cursor);
		}
		cursor.close();
		mDb.close();
		return values;
	}

	/**
	 * Adds images Uris to existing ContentValues
	 * @param id  is the Key of the Find whose images are sought
	 * @param values is an existing ContentValues with Find's <key, value> pairs
	 */
	public void getImages(long id, ContentValues values) {
		Cursor cursor = getImagesCursor(id);
		cursor.moveToFirst();
		Log.i(TAG, "Images count = " + cursor.getCount());
		if (cursor.getCount() != 0) {
			cursor.moveToFirst();
			for (String column : cursor.getColumnNames()) {
				String image = cursor.getString(cursor.getColumnIndexOrThrow(column));
				Log.i(TAG, "Column " + column + " = " + image);
				if (image != null)
					values.put(column, cursor.getString(cursor.getColumnIndexOrThrow(column)));
			}

		}
		cursor.close();
		mDb.close();
	}

	/**
	 * Adds videos Uris to existing ContentValues
	 * @param id  is the Key of the Find whose videos are sought
	 * @param values is an existing ContentValues with Find's <key, value> pairs
	 */
	public void getVideos(long id, ContentValues values) {
		Cursor cursor = getVideosCursor(id);
		cursor.moveToFirst();
		Log.i(TAG, "Videos count = " + cursor.getCount());
		if (cursor.getCount() != 0) {
			cursor.moveToFirst();
			for (String column : cursor.getColumnNames()) {
				String video = cursor.getString(cursor.getColumnIndexOrThrow(column));
				Log.i(TAG, "Column " + column + " = " + video);
				if (video != null) {
					values.put(column, cursor.getString(cursor.getColumnIndexOrThrow(column)));
				}
			}
		}
		cursor.close();
	}

	/**
	 * Adds audios Uris to existing ContentValues
	 * @param id  is the Key of the Find whose videos are sought
	 * @param values is an existing ContentValues with Find's <key, value> pairs
	 */
	public void getAudios(long id, ContentValues values) {
		Cursor cursor = getAudiosCursor(id);
		cursor.moveToFirst();
		Log.i(TAG, "Audios count = " + cursor.getCount());
		if (cursor.getCount() != 0) {
			cursor.moveToFirst();
			for (String column : cursor.getColumnNames()) {
				String audioClip = cursor.getString(cursor.getColumnIndexOrThrow(column));
				Log.i(TAG, "Column " + column + " = " + audioClip);
				if (audioClip != null) {
					values.put(column, cursor.getString(cursor.getColumnIndexOrThrow(column)));
				}
			}
		}
		cursor.close();
	}

	public ContentValues fetchBuildingColumns(long id, String[] columns) {
		mDb = getReadableDatabase();  
		String[] selectionArgs = null;
		String groupBy = null, having = null, orderBy = null;
		Cursor cursor = mDb.query(BUILDING_TABLE_NAME, columns, KEY_ID+"="+id, selectionArgs, groupBy, having, orderBy);
		cursor.moveToFirst();
		ContentValues values = null;
		if (cursor.getCount() != 0)
			values = getValuesFromRow(cursor);
		cursor.close();
		mDb.close();
		return values;
	}



	/**
	 * This helper method is passed a cursor, which points to a row of the DB.
	 *  It extracts the names of the columns and the values in the columns,
	 *  puts them into a ContentValues hash table, and returns the table.
	 * @param cursor is an object that manipulates DB tables. 
	 * @return
	 */
	private ContentValues getValuesFromRow(Cursor cursor) {
		ContentValues values = new ContentValues();
		cursor.moveToFirst();
		for (String column : cursor.getColumnNames()) {
			Log.i(TAG, "Column " + column + " = " + 
					cursor.getString(cursor.getColumnIndexOrThrow(column)));
			values.put(column, cursor.getString(cursor.getColumnIndexOrThrow(column)));
		}
		cursor.close();
		return values;
	}



	/**
	 * This method deletes all the images associated with a building or pretty much anything else.
	 * @param imagesQuery
	 * @return
	 */
	public boolean deleteImages (Cursor imagesQuery) {
		boolean result =true;
		while (imagesQuery.moveToNext()) {
			long id =imagesQuery.getLong(imagesQuery.getColumnIndexOrThrow(BaseColumns._ID));
			Log.i(TAG, ""+id);
			Uri uri = Uri.withAppendedPath(MediaStore.Images.Media.EXTERNAL_CONTENT_URI, id+"");
			int r = mContext.getContentResolver().delete(uri, null, null);
			result = result && (r>0); 
		}
		return result; //returns true if all the images are deleted
	}

	/**
	 * This method deletes all the videos associated with a building.
	 * @param videosQuery
	 * @return
	 */
	public boolean deleteVideos(Cursor videosQuery) {
		mDb = getWritableDatabase();
		boolean result = true;
		long id = videosQuery.getLong(videosQuery.getColumnIndexOrThrow(KEY_BUILDING_ID));
		int r = mDb.delete(VIDEO_TABLE_NAME, KEY_BUILDING_ID + " = " + id, null);
		mDb.close();
		videosQuery.close();
		result = result && (r > 0);
		return result; //returns true if all the videos are deleted
	}

	public boolean deleteAudioClips(Cursor audiosQuery) {
		mDb = getWritableDatabase();
		boolean result = true;
		long id = audiosQuery.getLong(audiosQuery.getColumnIndexOrThrow(KEY_BUILDING_ID));
		int r = mDb.delete(AUDIO_TABLE_NAME, KEY_BUILDING_ID + " = " + id, null);
		mDb.close();
		audiosQuery.close();
		result = result && (r > 0);
		return result; //returns true if all the audio clips are deleted
	}

	public Cursor getBuildingsWithIdentifier(long value) {
		mDb = getWritableDatabase();
		Cursor cursor = mDb.query(BUILDING_TABLE_NAME, null, KEY_IDENTIFIER + "=" + value, null, null, null, null);
		cursor.getCount();
		mDb.close();
		return cursor;
	}

	

}